package jdbc.dao;

import jdbc.DBUtil;
import jdbc.entity.Student;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;
import java.util.Optional;

/**
 * @author oldliu
 * @since 1.0
 */
public class StudentDao implements StudentDaoI {
    //CRUD
    @Override
    public int add(Student stu) {
        String sql="insert into s_student(sname,qq,wechat,gradeid)values(?,?,?,?)";
        Connection con = DBUtil.getConnection();
        PreparedStatement st = null;
        try {
            st=con.prepareStatement(sql);
            st.setString(1,stu.getSname());
            st.setString(2,stu.getQq());
            st.setString(3,stu.getWechat());
            st.setLong(4,stu.getGradeid());
            return st.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(con,st,null);
        }
        return 0;
    }
    //根据主键改其他列，一般只改一个实体
    @Override
    public int update(Student stu) {
        String sql = "update s_student set sname=?,qq=?,wechat=?,enterdate=? where stuno=?";
        return DBUtil.executeUpdate(sql, stu.getSname(), stu.getQq(), stu.getWechat(), stu.getEnterdate(), stu.getStuno());
    }

    @Override
    public int del(Student stu) {
        return DBUtil.executeUpdate("delete from s_student", stu.getStuno());
    }

    //根据主键，读取其他的信息
    @Override
    public Student get(long stuno) {
        String sql="select * from s_student where stuno=?";
        List<Student> list = DBUtil.query(sql, Student.class, stuno);
        return list.size()==0?null:list.get(0);
//        //不好
//
//        Connection con = DBUtil.getConnection();
//        PreparedStatement st = null;
//        ResultSet rs=null;
//        try {
//            st=con.prepareStatement(sql);
//            st.setLong(1,stuno);
//            rs=st.executeQuery();
//            if(rs.next()){
//                Student student=new Student();
//                student.setStuno(stuno);
//                student.setSname(rs.getString("sname"));
//                student.setAddress(rs.getString("address"));
//                student.setBirthday(rs.getDate("birthday"));
//                //....
//                return student;
//            }
//        } catch (SQLException e) {
//            e.printStackTrace();
//        } finally {
//            DBUtil.close(con,st,rs);
//        }
//        //???Optional<T>
//        return null;
    }
    //查询全部的学生
    @Override
    public List<Student> findALL() {
        List<Student> datas = new ArrayList<>();
        Connection connection = DBUtil.getConnection();
        Statement st = null;
        ResultSet rs = null;
        try {
            st = connection.createStatement();
            rs = st.executeQuery("select * from s_student");
            while (rs.next()) {
                Student student = new Student();
                student.setStuno(rs.getLong("stuno"));
                student.setSname(rs.getString("sname"));
                //..java.sql.Date
                student.setBirthday(rs.getDate("birthday"));
                datas.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs);
            DBUtil.close(st);
            DBUtil.close(connection);
        }

        return datas;
    }
    @Override
    public List<Student> search(int pageNo, int pageSize){
        return DBUtil.query("select * from s_student limit ?,?",Student.class,(pageNo-1)*pageSize,pageSize);
    }
    @Override
    public List<Student> search(String sname, String qq, String telephone){
        Connection connection = DBUtil.getConnection();
        String sql="select * from s_student where 1=1";
        List<Student> datas = new ArrayList<>();
        List<Object> params = new ArrayList<>();
        if(!(sname==null||sname.equals(""))){
            sql+=" and sname like ?";
            params.add("%"+sname+"%");
        }
        if(!(qq==null||qq.equals(""))){
            sql+=" and qq like ?";
            params.add("%"+qq+"%");
        }
        if(!(telephone==null||telephone.equals(""))){
            sql+=" and telephone like ?";
            params.add("%"+telephone+"%");
        }
        System.out.println(sql+",,,,"+params);
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            st = connection.prepareStatement(sql);
            for(int i=0;i<params.size();i++){
                st.setObject(i+1,params.get(i));
            }
            rs = st.executeQuery();
            while (rs.next()) {
                Student student = new Student();
                student.setStuno(rs.getLong("stuno"));
                student.setSname(rs.getString("sname"));
                student.setQq(rs.getString("qq"));
                student.setTelephone(rs.getString("telephone"));
                //..java.sql.Date
                student.setBirthday(rs.getDate("birthday"));
                datas.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs);
            DBUtil.close(st);
            DBUtil.close(connection);
        }

        return datas;
    }

    @Override
    public List<Student> search(int pageNo,int pageSize,String sname, String qq, String telephone){
        Connection connection = DBUtil.getConnection();
        String sql="select * from s_student where 1=1";
        List<Student> datas = new ArrayList<>();
        List<Object> params = new ArrayList<>();
        if(!(sname==null||sname.equals(""))){
            sql+=" and sname like ?";
            params.add("%"+sname+"%");
        }
        if(!(qq==null||qq.equals(""))){
            sql+=" and qq like ?";
            params.add("%"+qq+"%");
        }
        if(!(telephone==null||telephone.equals(""))){
            sql+=" and telephone like ?";
            params.add("%"+telephone+"%");
        }
        sql+=" limit ?,?";
        params.add((pageNo-1)*pageSize);
        params.add(pageSize);

        return DBUtil.query(sql,Student.class,params.toArray());
    }
}
